package dao.common;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import db.common.DBHelper;
import model.common.Employee;
import model.common.Manager;

public class ManagerDao {

	public void addManager(Manager m) throws SQLException {
		Connection conn = DBHelper.getConnection();
		String sql = " " + "insert into tab_manager " + "(managerName,managerAge,managerSex,"
				+ " managerEmail,managerAddress,managerIdentifyId,managerPassword,managerTelephone) "
				+ " values(?,?,?,?,?,?,?,?)  ";

		PreparedStatement ptmt = conn.prepareStatement(sql);
		ptmt.setString(1, m.getManagerName());
		ptmt.setString(2, m.getManagerAge());
		ptmt.setString(3, m.getManagerSex());
		ptmt.setString(4, m.getManagerEmail());
		ptmt.setString(5, m.getManagerAddress());
		ptmt.setString(6, m.getManagerIdentifyId());
		ptmt.setString(7, m.getManagerPassword());
		ptmt.setString(8, m.getManagerTelephone());

		ptmt.execute();
	}

	public void deleteManager(Integer managerId) throws SQLException {
		Connection conn = DBHelper.getConnection();
		String sql = " delete from tab_manager  where managerId=? ";
		PreparedStatement ptmt = conn.prepareStatement(sql);
		ptmt.setInt(1, managerId);
		ptmt.execute();
	}

	public void updateManager(Manager m) throws SQLException {
		Connection conn = DBHelper.getConnection();
		String sql = " update tab_manager set managerName=?,managerAge=?,managerSex=?,managerEmail=?,"
				+ " managerAddress=?,managerIdentifyId=?,managerPassword=?,managerTelephone=?" 
				+ " where managerId=? ";
		PreparedStatement ptmt = conn.prepareStatement(sql);

		ptmt.setString(1, m.getManagerName());
		ptmt.setString(2, m.getManagerAge());
		ptmt.setString(3, m.getManagerSex());
		ptmt.setString(4, m.getManagerEmail());
		ptmt.setString(5, m.getManagerAddress());
		ptmt.setString(6, m.getManagerIdentifyId());
		ptmt.setString(7, m.getManagerPassword());
		ptmt.setString(8, m.getManagerTelephone());
		ptmt.setInt(9, m.getManagerId());

		ptmt.execute();
	}

	public Manager get(Integer managerId) throws SQLException {
		Manager m = null;
		Connection conn = DBHelper.getConnection();
		String sql = " select * from tab_manager where managerId=? ";
		PreparedStatement ptmt = conn.prepareStatement(sql);
		ptmt.setInt(1, managerId);
		ResultSet rs = ptmt.executeQuery();
		while (rs.next()) {
			m = new Manager();
			m.setManagerId(rs.getInt("managerId"));
			m.setManagerName(rs.getString("managerName"));
			m.setManagerAge(rs.getString("managerAge"));
			m.setManagerSex(rs.getString("managerSex"));
			m.setManagerEmail(rs.getString("managerEmail"));
			m.setManagerAddress(rs.getString("managerAddress"));
			m.setManagerIdentifyId(rs.getString("managerIdentifyId"));
			m.setManagerPassword(rs.getString("managerPassword"));
			m.setManagerTelephone(rs.getString("managerTelephone"));
			System.out.println("a");
		}
		return m;
	}

	public List<Manager> query(List<Map<String, Object>> params) throws SQLException {
		List<Manager> result = new ArrayList<Manager>();
		Connection conn = DBHelper.getConnection();
		StringBuffer sb = new StringBuffer();
		sb.append("select * from tab_manager where 1=1 ");
		if (params != null && params.size() > 0) {
			for (int i = 0; i < params.size(); i++) {
				Map<String, Object> map = params.get(i);
				sb.append(" and " + map.get("name") + "  " + map.get("rela") + "  " + map.get("value"));
			}
		}
		PreparedStatement ptmt = conn.prepareStatement(sb.toString());

		System.out.println(sb.toString());
		ResultSet rs = ptmt.executeQuery();

		Manager m = null;
		while (rs.next()) {
			m = new Manager();
			m.setManagerId(rs.getInt("managerId"));
			m.setManagerAge(rs.getString("managerAge"));
			m.setManagerSex(rs.getString("managerSex"));
			m.setManagerEmail(rs.getString("managerEmail"));
			m.setManagerAddress(rs.getString("managerAddress"));
			m.setManagerIdentifyId(rs.getString("managerIdentifyId"));
			m.setManagerPassword(rs.getString("managerPassword"));
			m.setManagerTelephone(rs.getString("managerTelephone"));
			System.out.println("a");
			result.add(m);
		}

		return result;
	}

	public List<Manager> query() throws Exception {
		List<Manager> result = new ArrayList<Manager>();

		Connection conn = DBHelper.getConnection();
		StringBuilder sb = new StringBuilder();
		sb.append("select managerId,managerName,managerAge from tab_manager  ");

		PreparedStatement ptmt = conn.prepareStatement(sb.toString());

		ResultSet rs = ptmt.executeQuery();

		Manager m = null;
		while (rs.next()) {
			m = new Manager();
			m.setManagerId(rs.getInt("managerId"));
			m.setManagerName(rs.getString("managerName"));
			m.setManagerAge(rs.getString("managerAge"));
			result.add(m);
		}
		return result;
	}

	public List<Manager> query(String managerName, String managerPassword) throws Exception {
		List<Manager> result = new ArrayList<Manager>();

		Connection conn = DBHelper.getConnection();
		StringBuilder sb = new StringBuilder();
		sb.append("select * from tab_manager  ");

		sb.append(" where managerName like ? and managerPassword like ? ");

		PreparedStatement ptmt = conn.prepareStatement(sb.toString());
		ptmt.setString(1, "%" + managerName + "%");
		ptmt.setString(2, "%" + managerPassword + "%");

		System.out.println(sb.toString());
		ResultSet rs = ptmt.executeQuery();

		Manager m = null;
		while (rs.next()) {
			m = new Manager();
			m.setManagerId(rs.getInt("managerId"));
			m.setManagerName(rs.getString("managerName"));
			m.setManagerAge(rs.getString("managerAge"));
			m.setManagerSex(rs.getString("managerSex"));
			m.setManagerEmail(rs.getString("managerEmail"));
			m.setManagerAddress(rs.getString("managerAddress"));
			m.setManagerIdentifyId(rs.getString("managerIdentifyId"));
			m.setManagerPassword(rs.getString("managerPassword"));
			m.setManagerTelephone(rs.getString("managerTelephone"));
			result.add(m);
		}
		return result;
	}

	public Manager getLoginManagerName(String managerName) {
		Connection conn = null;
		PreparedStatement ptmt = null;
		ResultSet rs = null;
		try {
			conn = DBHelper.getConnection();
			String sql = "select * from tab_manager where managerName=?";
			ptmt = conn.prepareStatement(sql);
			ptmt.setString(1, managerName);
			rs = ptmt.executeQuery();
			Manager m = new Manager();
			if (rs.next()) {
				m.setManagerId(rs.getInt("managerId"));
				m.setManagerAge(rs.getString("managerAge"));
				m.setManagerSex(rs.getString("managerSex"));
				m.setManagerEmail(rs.getString("managerEmail"));
				m.setManagerAddress(rs.getString("managerAddress"));
				m.setManagerIdentifyId(rs.getString("managerIdentifyId"));
				m.setManagerPassword(rs.getString("managerPassword"));
				m.setManagerTelephone(rs.getString("managerTelephone"));
				return m;
			} else {
				return null;
			}
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			DBHelper.close(rs);
		}
	}

}
